package com.zii.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zii.mapper.UserMapper;
import com.zii.pojo.User;
import com.zii.service.PoiService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import org.springframework.util.CollectionUtils;

import java.io.ByteArrayOutputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @version 1.0
 * @description: TODO
 * @author: liu zi yi
 * @date 2024/9/18 9:49
 */
@Service
public class PoiServiceImpl extends ServiceImpl<UserMapper, User> implements PoiService {
    @Autowired
    private DataSource dataSource;

    @Override
    public void exportMillion(HttpServletRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
        int page = 1;
        int pageSize = 200000;
        int rowIndex = 1;
        int fileIndex = 1;
        response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户信息表.zip".getBytes(), "ISO8859-1"));
        response.setContentType("application/zip");
        try {
            ZipOutputStream zipOutputStream = new ZipOutputStream(response.getOutputStream());
            ByteArrayOutputStream arrayOutputStream = new ByteArrayOutputStream();
            Workbook workbook = new SXSSFWorkbook();
            Sheet sheet = workbook.createSheet("第" + fileIndex + "个工作表");
            sheet.setColumnWidth(0, 8 * 256);
            sheet.setColumnWidth(1, 12 * 256);
            sheet.setColumnWidth(2, 15 * 256);
            sheet.setColumnWidth(3, 15 * 256);
            sheet.setColumnWidth(4, 30 * 256);
            String[] titles = new String[]{"编号", "姓名", "手机号", "身份证号", "地址"};
            Row titleRow = sheet.createRow(0);
            Cell cell;
            for (int i = 0; i < titles.length; i++) {
                cell = titleRow.createCell(i);
                cell.setCellValue(titles[i]);
            }
            while (true) {
                List<User> userList = this.findPage(page, pageSize);
                if (CollectionUtils.isEmpty(userList)) {
                    break;
                }
                for (User user : userList) {
                    if (rowIndex > 1000000) {
                        workbook.write(arrayOutputStream);
                        zipOutputStream.putNextEntry(new ZipEntry("用户信息_" + fileIndex + ".xlsx"));
                        zipOutputStream.write(arrayOutputStream.toByteArray());
                        zipOutputStream.closeEntry();
                        arrayOutputStream.reset();

                        // Close and create a new workbook
                        workbook.close();
                        workbook = new SXSSFWorkbook();
                        sheet = workbook.createSheet("第" + ++fileIndex + "个工作表");
                        sheet.setColumnWidth(0, 8 * 256);
                        sheet.setColumnWidth(1, 12 * 256);
                        sheet.setColumnWidth(2, 15 * 256);
                        sheet.setColumnWidth(3, 15 * 256);
                        sheet.setColumnWidth(4, 30 * 256);

                        titleRow = sheet.createRow(0);
                        for (int i = 0; i < titles.length; i++) {
                            cell = titleRow.createCell(i);
                            cell.setCellValue(titles[i]);
                        }
                        rowIndex = 1;
                    }

                    Row row = sheet.createRow(rowIndex++);
                    cell = row.createCell(0);
                    cell.setCellValue(user.getUserId());

                    cell = row.createCell(1);
                    cell.setCellValue(user.getUserName());

                    cell = row.createCell(2);
                    cell.setCellValue(user.getMobile());

                    cell = row.createCell(3);
                    cell.setCellValue(user.getIdentity());

                    cell = row.createCell(4);
                    cell.setCellValue(user.getAddress());
                }
                page++;
            }

            // Write the last workbook if there are remaining rows
            if (rowIndex > 1) {
                workbook.write(arrayOutputStream);
                zipOutputStream.putNextEntry(new ZipEntry("用户信息_" + fileIndex + ".xlsx"));
                zipOutputStream.write(arrayOutputStream.toByteArray());
                zipOutputStream.closeEntry();
            }

            workbook.close();
            arrayOutputStream.close();
            zipOutputStream.finish();
        } catch (Exception e) {
            e.printStackTrace();
            // Handle exception as needed
        }
    }

    private List<User> findPage(int page, int pageSize) {
        String sql = "SELECT user_id,name,user_name,mobile,pwd,mailbox,identity,address FROM user where user_id > ? LIMIT ?";
        List<User> users = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            int offset = (page - 1) * pageSize;
            stmt.setInt(1, offset);
            stmt.setInt(2, pageSize);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    User user = new User();
                    user.setUserId(rs.getInt("user_id"));
                    user.setName(rs.getString("name"));
                    user.setUserName(rs.getString("user_name"));
                    user.setMobile(rs.getString("mobile"));
                    user.setPwd(rs.getString("pwd"));
                    user.setMailbox(rs.getString("mailbox"));
                    user.setIdentity(rs.getString("identity"));
                    user.setAddress(rs.getString("address"));
                    users.add(user);
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return users;
    }
}
